#-*- encoding: utf-8 -*-
import win32com.client 
from Misc.Utils import *

from DataAccess.QueryBlp import *

from datetime import date,timedelta

from ReportTool.HldReport import *
from ReportTool.PnLReport import *
from ReportTool.NAVReport import *
from ReportTool.MktValReport import *
from ReportTool.TradeReport import *
from ReportTool.RealizedPnL import *
from ReportTool.OnshoreHldReport import *

from Config.ReportConfig import *

def PingAn_Monthly_Report_One_Portf(ref_date, portf_id):

	if portf_id == 'SC008' or portf_id == 'SC010':
		entity_list = [ portf_id + '_A' ]
	else:
		entity_list = [ portf_id + '_F' ]
	
	nvpu_crt = query_latest_NAV(ref_date, entity_list, is_official=True)
	nvpu_icpt = query_inception_NAV(entity_list)
	nvpu_pre_1m = query_latest_NAV(end_of_mth(ref_date, -1), entity_list, is_official=True)
	nvpu_pre_3m = query_latest_NAV(end_of_mth(ref_date, -3), entity_list, is_official=True)
	nvpu_pre_6m = query_latest_NAV(end_of_mth(ref_date, -6), entity_list, is_official=True)
	nvpu_pre_12m = query_latest_NAV(end_of_mth(ref_date, -12), entity_list, is_official=True)
	nvpu_series = query_NAV_series(date(2007, 1, 1), ref_date, entity_list)
	hld = query_hld(ref_date, portf_list = [ portf_id ])
	
	tplt_file = TPLT_PATH + 'Monthly Report for PingAn.xls'
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(tplt_file)
	xSht = xBook.Worksheets('Data')
	
	xSht.Cells(2, 7).Value = fund_name_in_report(portf_id)
	xSht.Range(xSht.Cells(3, 2), xSht.Cells(3, 3)).Value = nvpu_crt[0][1:]
	xSht.Range(xSht.Cells(4, 2), xSht.Cells(4, 3)).Value = nvpu_icpt[0][1:]
	xSht.Range(xSht.Cells(5, 2), xSht.Cells(5, 3)).Value = nvpu_pre_1m[0][1:]
	xSht.Range(xSht.Cells(6, 2), xSht.Cells(6, 3)).Value = nvpu_pre_3m[0][1:]
	xSht.Range(xSht.Cells(7, 2), xSht.Cells(7, 3)).Value = nvpu_pre_6m[0][1:]
	xSht.Range(xSht.Cells(8, 2), xSht.Cells(8, 3)).Value = nvpu_pre_12m[0][1:]
	xSht.Cells(3, 4).Formula = '=s_dq_close("000001.SH",$B$3,1)'
	xSht.Cells(4, 4).Formula = '=s_dq_close("000001.SH",$B$4,1)'
	xSht.Cells(5, 4).Formula = '=s_dq_close("000001.SH",$B$5,1)'
	xSht.Cells(6, 4).Formula = '=s_dq_close("000001.SH",$B$6,1)'
	xSht.Cells(7, 4).Formula = '=s_dq_close("000001.SH",$B$7,1)'
	xSht.Cells(8, 4).Formula = '=s_dq_close("000001.SH",$B$8,1)'
	
	row = 3
	for elem in nvpu_series:
		xSht.Cells(row, 6).Value = elem[1]
		xSht.Cells(row, 7).Value = elem[2]
		xSht.Cells(row, 8).Formula = '=s_dq_close("000001.SH",F' + str(row) + ',1)/s_dq_close("000001.SH",$F$3,1)*$G$3'
		row += 1
		
	row = 3
	for elem in hld:
		if elem[2] == 'STOCK':
			xSht.Cells(row, 10).Value = elem[0]
			xSht.Cells(row, 11).Value = elem[12]
			xSht.Cells(row, 12).Formula = '=s_info_industry_sw(J' + str(row) + ',1)'
			row += 1

	rept_file = REPT_PATH + 'Monthly Report for PingAn_''' + portf_id + '_' + ref_date.strftime("%Y%m%d") + '.xls'
	xBook.SaveAs(rept_file)
	xBook.Close()
	del xApp

def PingAn_Monthly_Report(ref_date):
	portf_list = ['SC001', 'SC002', 'SC003', 'SC004', 'SC005', 'SC006',
					'SC007', 'SC008', 'SC009', 'SC010', 'SC013', 'SC031']
	#portf_list = ['SC031']
	
	for portf_id in portf_list:
		PingAn_Monthly_Report_One_Portf(ref_date, portf_id)
		
